package dao;

import bean.DealRecord;
import bean.PageBean;
import bean.User;
import util.Connect;
import util.DealRecordUtil;
import util.JdbcUtils;
import util.StringUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 交易记录的Dao
 *
 * @author dsf
 */
public class DealRecordDao {

    private Connect con = Connect.getInstance();

    /**
     * Deal record search list.
     *
     * @param pageBean   the page bean
     * @param dealrecord the deal record
     * @param orTag      the or tag
     * @return list
     * @throws Exception the exception
     */
    public List<Object> dealrecordSearch(PageBean pageBean, DealRecord dealrecord, String orTag) throws Exception {
        StringBuilder sb = new StringBuilder("select * from dealrecord where ");

        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(dealrecord.getDealRecordId())) {
            sb.append("dealrecordID=?");
            para.add(dealrecord.getDealRecordId());
        }
        if (StringUtil.isNotEmpty(dealrecord.getUserId())) {
            sb.append("userId=?");
            para.add(dealrecord.getUserId());
        }
        if (StringUtil.isNotEmpty(dealrecord.getActionTime())) {
            sb.append(" and actionTime=?");
            para.add(dealrecord.getActionTime());
        }
        if (StringUtil.isNotEmpty(dealrecord.getAlipayStatus())) {
            if (StringUtil.isNotEmpty(orTag)) {
                sb.append(" and alipayStatus in (? ,?) order by actiontime desc");
                para.add("0");
                para.add("2");
            } else {
                sb.append("alipayStatus=?");
                para.add(dealrecord.getAlipayStatus());
            }
        }
        if (pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getRows());
        }

        String sql = sb.toString();
        return con.queryForArrObject(sql, para, DealRecord.class);
    }

    /**
     * 交易记录的条目数量统计
     *
     * @param dealrecord the deal record
     * @param orTag      the or tag
     * @return int
     * @throws Exception the exception
     */
    public int dealrecordCount(DealRecord dealrecord, String orTag) throws Exception {
        StringBuilder sb = new StringBuilder("select count(*) as total from dealrecord");

        List<Object> para = new ArrayList<Object>();
        if (StringUtil.isNotEmpty(dealrecord.getUserId())) {
            sb.append("userId=?");
            para.add(dealrecord.getUserId());
        }
        if (StringUtil.isNotEmpty(orTag)) {
            sb.append(" and alipayStatus=? and ? order by actiontime desc");
            para.add("0");
            para.add("2");
        }
        String sql = sb.toString();
        List<Object> list = con.queryForArrObject(sql, para, DealRecord.class);
        if (!list.isEmpty()) {
            return (Integer) list.get(0);
        } else {
            return 0;
        }
    }

    /**
     * 交易记录删除（byId）
     *
     * @param dealrecordId the deal record id
     * @return int
     * @throws Exception the exception
     */
    public int dealrecordDelete(String dealrecordId) throws Exception {
        List<Object> para = new ArrayList<>();
        para.add(dealrecordId);
        String sql = "delete * from dealrecord where dealrecordid=?";
        return con.update(sql, para);
    }

    /**
     * 交易记录增加
     *
     * @param dealrecord the deal record
     * @return int
     * @throws Exception the exception
     */
    public int dealrecordAdd(DealRecord dealrecord) throws Exception {
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(dealrecord.getDealRecordId())) {
            para.add(dealrecord.getDealRecordId());
        }
        if (StringUtil.isNotEmpty(dealrecord.getUserId())) {

            para.add(dealrecord.getUserId());
        }
        if (StringUtil.isNotEmpty(dealrecord.getActionType())) {

            para.add(dealrecord.getActionType());
        }
        if (StringUtil.isNotEmpty(dealrecord.getMoney())) {

            para.add(dealrecord.getMoney());
        }
        if (StringUtil.isNotEmpty(dealrecord.getActionTime())) {
            para.add(dealrecord.getActionTime());
        }
        if (StringUtil.isNotEmpty(dealrecord.getCurBalance())) {
            para.add(dealrecord.getCurBalance());
        }
        if (StringUtil.isNotEmpty(dealrecord.getAlipayStatus())) {
            para.add(dealrecord.getAlipayStatus());
        }
        String sql = "insert into dealrecord values( ?, ?, ?, ?, ?, ?, ?)";
        return con.update(sql, para);
    }

    /**
     * 交易记录修改（更改字段alipayStatus的值）
     *
     * @param dealrecord the deal record
     * @return int
     * @throws Exception the exception
     */
    public int dealrecordModify(DealRecord dealrecord) throws Exception {
        StringBuilder sb = new StringBuilder("update dealrecord set ");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(dealrecord.getDealRecordId())) {
            sb.append("alipayStatus=? ");
            para.add(dealrecord.getAlipayStatus());
        }
        sb.append("where dealrecordid=?");
        para.add(dealrecord.getDealRecordId());
        return con.update(sb.toString(), para);
    }

    /**
     * Recharge.
     *
     * @param connection the connection
     * @param user       the user
     * @param money      the money
     * @throws SQLException the sql exception
     */
    public void recharge(Connection connection, User user, BigDecimal money) throws SQLException {
        String sql = "INSERT INTO dealrecord(dealrecordId, userId, actionType, money, actionTime, curbalance, alipayStatus) VALUES (?,?,?,?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        String now = DealRecordUtil.getTimeNow();
        ps.setString(1, DealRecordUtil.getDrId(user.getUserId(), "1", now));
        ps.setString(2, user.getUserId());
        ps.setString(3, "1");
        ps.setString(4, money.toPlainString());
        ps.setString(5, now);
        ps.setString(6, DealRecordUtil.formatBalance(user.getBalance()));
        ps.setString(7, "0");
        ps.executeUpdate();
        JdbcUtils.closeStatement(ps);
    }

    /**
     * Today consume and recharge map.
     *
     * @param userId the user id
     * @return the map
     */
    public Map<String, BigDecimal> todayConsumeAndRecharge(String userId) {
        String sql = "SELECT d.actionType AS type, sum(money) AS money " +
            "FROM dealrecord d\n" +
            "WHERE d.userId = ? AND DATE(d.actionTime) = curdate() " +
            "GROUP BY d.actionType";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String, BigDecimal> r = new HashMap<>(10);
        r.put("consume", BigDecimal.ZERO);
        r.put("recharge", BigDecimal.ZERO);
        try {
            conn = con.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, userId);
            rs = ps.executeQuery();
            while (rs.next()) {
                byte type = rs.getByte("type");
                BigDecimal bg = rs.getBigDecimal("money");
                r.put(type == 0 ? "consume" : "recharge", bg);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            con.freeResultSet(rs);
            con.freeStatement(ps);
            con.freeConnection(conn);
        }
        return r;
    }
}